﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;


namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingCoursesDesDtlsDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataSet GetData(Nullable<int> EmpSeq, Nullable<int> TrainSeq, Nullable<int> Granted)
        { 
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(4);
            dbManager.AddParameters(0, "@emp_seq", EmpSeq);
            dbManager.AddParameters(1, "@train_seq", TrainSeq);
            dbManager.AddParameters(2, "@record_status", SystemInfo._DeleteRecordStatus);
            dbManager.AddParameters(3, "@granted", Granted);


            string sql = "SELECT pe.first_name"
                        + ", pe.last_name"
                        + ", tt.train_seq"
                        + ", tt.trainee_seq"
                        + ", tt.emp_seq"
                        + ", tt.result_memo"
                        + ", tt.trainee_text"
                        + ", tt.train_hour"
                        + ", tt.working_text"
                        + ", tt.position_text"
                        + ", tt.class_text"
                        + ", tt.created_when"
                        + ", tt.granted"
                        + ", tc.training_course_descr"
                        + ", tc.date_sign_start"
                        + ", tc.date_training_start"
                        + ", tc.closed"
                        + ", pe.name_prefix_seq"
                        + ", pe.first_name"
                        + ", pe.last_name"
                        + ", np.prefix_short"
                        + ", CASE WHEN tc.closed not like 0 THEN 'ปิด' ELSE 'เปิด' END AS Status"
                        + ", CASE WHEN tt.granted = 0 then 'อยู่ระหว่างการพิจารณ'"
                        + " WHEN tt.granted = 1 then 'อนุมัติ' ELSE 'ไม่อนุมัติ' END AS Approve"
                        + ", CONCAT(np.prefix_short, pe.first_name, ' ', pe.last_name) AS FULL_NAME"
                        + ", CASE WHEN tt.score = 1 then 'ผ่าน'"
                        + " WHEN tt.score = 0 then 'ไม่ผ่าน'"
                        + " WHEN tt.score > 1 then ' ' end as T_SCORE"
                        + ", CASE WHEN tt.score > 1 then cast(tt.score as CHAR) else ' ' end NUM_SCORE"
                        + ", (select sum(quota) from hrm_training_courses_quota tq where tq.train_seq = tt.train_seq GROUP BY train_seq ) AS COUNT_QUTA"
                        + " FROM hrm_training_courses_trainees tt"
                        + " left join hrm_ps_employees pe ON tt.emp_seq = pe.emp_seq"
                        + " left join mas_name_prefixes np ON pe.name_prefix_seq = np.name_prefix_seq"
                        + " left join hrm_training_courses tc ON tt.train_seq = tc.train_seq"
                        + " WHERE tt.record_status not like @record_status"
                        + " AND tc.record_status not like @record_status"
                        + (TrainSeq != null ? " AND tt.train_seq = @train_seq" : "")
                        + (Granted != null ? " AND tt.granted = @granted " : "")
                        + (EmpSeq != null ? " AND pe.emp_seq = @emp_seq" : "");

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
               
        }
        public DataSet GetDataTreeView()
        { 
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

            string sql = "select * from("
                + " select 100 + train_seq as id, training_course_descr as name, 0 as parent_id"
                + " from hrm_training_courses"
                + " where record_status not like @DeleteRecord"
                + " union all"
                + " select n.trainee_seq as id, concat(np.prefix_short, e.first_name, ' ', e.last_name) as name"
                    + ", 100 + n.train_seq as parent_id"
                + " from hrm_training_courses_trainees n"
                + " left join hrm_ps_employees e on n.emp_seq = e.emp_seq"
                + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                + " where granted = 1"
                + ")t";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
        public void InsertByTrainee(int TrainSeq, Nullable<int> EmpSeq, string Name, Nullable<int> JobSite, string WorkText, string Position, string Class, Nullable<int> Crateby)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@train_seq", TrainSeq);
                dbManager.AddParameters(1, "@emp_seq", EmpSeq);
                dbManager.AddParameters(2, "@trainee_text", Name);
                dbManager.AddParameters(3, "@job_site_seq", JobSite);
                dbManager.AddParameters(4, "@working_text", WorkText);
                dbManager.AddParameters(5, "@position_text", Position);
                dbManager.AddParameters(6, "@class_text", Class);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", Crateby);
                dbManager.AddParameters(9, "@created_when", DateTime.Today);

                strSQL = "INSERT hrm_training_courses_trainees"
                    + "(train_seq"
                    + ", emp_seq"
                    + ", trainee_text"
                    + ", job_site_seq"
                    + ", working_text"
                    + ", position_text"
                    + ", class_text"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@train_seq"
                    + ", @emp_seq"
                    + ", @trainee_text"
                    + ", @job_site_seq"
                    + ", @working_text"
                    + ", @position_text"
                    + ", @class_text"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertQuta(Nullable<int> TraineeSeq, int TrainSeq, Nullable<int> EmpSeq, string CommentText, Nullable<int> TrainResult, Nullable<int> TrainHour, Nullable<int> scored_by, Nullable<int> Granted)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@train_seq", TrainSeq);
                dbManager.AddParameters(1, "@emp_seq", EmpSeq);
                dbManager.AddParameters(2, "@score", TrainResult);
                dbManager.AddParameters(3, "@train_hour", TrainHour);
                dbManager.AddParameters(4, "@scored_by", scored_by);
                dbManager.AddParameters(5, "@scored_when", DateTime.Today);
                dbManager.AddParameters(6, "@granted", Granted);
                dbManager.AddParameters(7, "@result_memo", CommentText);
                dbManager.AddParameters(8, "@trainee_seq", TraineeSeq);


                strSQL = "UPDATE  hrm_training_courses_trainees"
                    + " SET score = @score"
                    + ", train_hour = @train_hour"
                    + ", granted = @granted"
                    + ", result_memo = @result_memo"
                    + ", scored_by = @scored_by"
                    + ", scored_when = @scored_when"
                    + " WHERE train_seq = @train_seq"
                    + " AND emp_seq = @emp_seq"
                    + (TraineeSeq != null ? " AND trainee_seq = @trainee_seq " : ""); 

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateTrainee(int TraineeSeq, int TrainSeq, Nullable<int> EmpSeq, string Name,Nullable<int> JobSite, string WorkText, string Position, string Class, Nullable<int> UpdatedBy)
    {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@train_seq", TrainSeq);
                dbManager.AddParameters(1, "@emp_seq", EmpSeq);
                dbManager.AddParameters(2, "@working_text", WorkText);
                dbManager.AddParameters(3, "@position_text", Position);
                dbManager.AddParameters(4, "@class_text", Class);
                dbManager.AddParameters(5, "@trainee_text", Name);
                dbManager.AddParameters(6, "@job_site_seq", JobSite);
                dbManager.AddParameters(7, "@trainee_seq", TraineeSeq);
                dbManager.AddParameters(8, "@updated_by", UpdatedBy);
                dbManager.AddParameters(9, "@updated_when", DateTime.Today);

                strSQL = "UPDATE  hrm_training_courses_trainees"
                    + " SET train_seq = @train_seq"
                    + ", emp_seq = @emp_seq"
                    + ", working_text = @working_text"
                    + ", position_text = @position_text"
                    + ", class_text = @class_text"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE trainee_seq = @trainee_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteQuta(int TraineeSeq, Nullable<int> UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@trainee_seq", TraineeSeq);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@updated_when", DateTime.Today);
                dbManager.AddParameters(3, "@granted", "0");

                strSQL = "UPDATE  hrm_training_courses_trainees"
                    + " SET granted = @granted"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE trainee_seq = @trainee_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void DeleteTrainee(int TraineeSeq, Nullable<int> UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                
                dbManager.AddParameters(0, "@trainee_seq", TraineeSeq);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@updated_when", DateTime.Today);
                dbManager.AddParameters(3, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "UPDATE  hrm_training_courses_trainees"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE trainee_seq = @trainee_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
